<?php

include_once 'DBCon.php';
/*
 * To change this template, choose Tools | Templates
 * AND open the template in the editor.
 */

/**
 * Description of Ticket
 *
 * @author user
 */
class Ticket {

    private $referenceno;
    private $departuredate;
    private $seatNo;
    private $coach;
    private $departure;
    private $arrival;
    private $origin;
    private $destination;
    private $class;
    private $status;
    private $fare;
    private $icno;
    private $discountid;
    private $shid;
    private $salt = "A3/-osv$0-231@#SC34zsd8fq]tj34&41dsjyj70u2%7vw4>[:l-==";
    private $con;
    private $generateticket = 'INSERT INTO ticket(departureDate, departure, arrival, 
        origin, destination, class, fare, status, icNo, discountId, shid) VALUES(?,?,?,?,?,?,?,?,?,?,?);';
    private $getseatbyqrcode = 'SELECT seatNo, coach
        from ticketqrcode tq, bookedseat bs, seat s WHERE tq.referenceNo = bs.referenceNo AND bs.seatid = s.seatid AND code = ?;';
    private $getrecentticket = 'SELECT referenceNo, class, origin, destination, departureDate, departure, datePurchased FROM ticket WHERE icNo = ? ORDER BY datePurchased DESC LIMIT 0, 5;';
    private $viewallticket = 'SELECT referenceNo, class, origin, destination, departureDate, departure, datePurchased FROM ticket WHERE icNo = ? ORDER BY datePurchased DESC;';
    private $viewoneticket = 'SELECT t.referenceNo, departureDate, datePurchased, departure, arrival, origin, destination, class, seatNo, coach, routeName, t.status, code 
        FROM ticket t, ticketqrcode tq, bookedseat bs, seat s, schedule sh, route r 
        WHERE tq.referenceNo = t.referenceNo AND bs.referenceNo = t.referenceNo AND s.seatid = bs.seatid 
        AND bs.shid = sh.shid AND sh.rid = r.rid AND t.referenceNo = ? AND icNo = ?;';
    private $selectticketdetails = 'SELECT UNIX_TIMESTAMP(CONCAT(departureDate," ", departure)) AS departure, fare, status FROM ticket WHERE referenceNo = ?;';
    private $cancelticket = 'UPDATE ticket SET status = "CCL" WHERE referenceNo = ?;';
    private $amendticket = 'UPDATE ticket SET departureDate = ?, departure = ?, arrival = ?, class = ?, status = "AMD", shid = ? WHERE referenceNo = ?;';
    private $updateqrcode = 'UPDATE ticketqrcode SET code = ? WHERE referenceNo = ?;';

    function __construct() {
        $this->con = DBCon::connectDB();
    }

    public function createTicket($departuredate, $departure, $arrival, $origin, $destination, $class, $status, $icno, $discountid, $shid) {
        $this->departuredate = $departuredate;
        $this->departure = $departure;
        $this->arrival = $arrival;
        $this->origin = $origin;
        $this->destination = $destination;
        $this->class = $class;
        $this->status = $status;
        $this->icno = $icno;
        $this->discountid = $discountid;
        $this->shid = $shid;
    }

    public function generateTicket($departuredate, $departure, $arrival, $origin, $destination, $class, $fare, $status, $icno, $discountid, $shid) {
        $pstmtinsert = $this->con->prepare($this->generateticket);
        $pstmtinsert->bind_param('ssssssdssii', $departuredate, $departure, $arrival, $origin, $destination, $class, $fare, $status, $icno, $discountid, $shid);
        $pstmtinsert->execute();
        $this->referenceno = $pstmtinsert->insert_id;
        $param = $this->referenceno . ' ' . $departuredate . ' ' . $departure . ' ' . $arrival . ' ' . $origin . ' ' . $destination . ' ' . $class . ' ' . $fare;
        $hashed = hash('sha256', $this->salt . $param);
        $pstmtinsertqr = $this->con->prepare('INSERT INTO ticketqrcode VALUES(?,?);');
        $pstmtinsertqr->bind_param('si', $hashed, $this->referenceno);
        if ($pstmtinsert->affected_rows > 0) {
            $pstmtinsertqr->execute();
            return array('reference' => $this->referenceno, 'qrcode' => $hashed);
        } else {
            return null;
        }
    }

    public function getSeatAndCoach($code) {
        $pstmtselect = $this->con->prepare($this->getseatbyqrcode);
        $pstmtselect->bind_param('s', $code);
        $pstmtselect->bind_result($this->seatNo, $this->coach);
        $pstmtselect->execute();
        if ($pstmtselect->fetch()) {
            return array(
                'seatNo' => $this->seatNo,
                'coach' => $this->coach
            );
        } else {
            return null;
        }
    }

    public function getRecentTicket($icno) {
        $result = null;
        $data = null;
        $pstmtselect = $this->con->prepare($this->getrecentticket);
        $pstmtselect->bind_param('s', $icno);
        $pstmtselect->bind_result($result['referenceno'], $result['class'], $result['origin'], $result['destination'], $result['departuredate'], $result['departure'], $result['datepurchased']);
        $pstmtselect->execute();
        while ($pstmtselect->fetch()) {
            foreach ($result as $key => $value) {
                $row[$key] = $value;
            }
            $data[] = $row;
        }
        if ($data) {
            return $data;
        } else {
            return NULL;
        }
    }

    public function viewAllPurchasedTicket($icno) {
        $result = array();
        $data = null;
        $pstmtselect = $this->con->prepare($this->viewallticket);
        $pstmtselect->bind_param('s', $icno);
        $pstmtselect->bind_result($result['referenceno'], $result['class'], $result['origin'], $result['destination'], $result['departuredate'], $result['departure'], $result['datepurchased']);
        $pstmtselect->execute();
        while ($pstmtselect->fetch()) {
            foreach ($result as $key => $value) {
                $row[$key] = $value;
            }
            $data[] = $row;
        }
        return $data;
    }

    public function viewOneTicket($referenceno, $icno) {
        $result = array();
        $data = null;
        $pstmtselect = $this->con->prepare($this->viewoneticket);
        $pstmtselect->bind_param('ss', $referenceno, $icno);
        $pstmtselect->bind_result($result['referenceno'], $result['departuredate'], $result['purchasedate'], $result['departure'], $result['arrival'], $result['origin'], $result['destin'], $result['class'], $result['seatno'], $result['coach'], $result['routename'], $result['status'], $result['code']);
        $pstmtselect->execute();
        if ($pstmtselect->fetch()) {
            foreach ($result as $key => $value) {
                $row[$key] = $value;
            }
            $data = $row;
        }
        return $data;
    }

    public function getCancellationDetails($referenceno) {
        $data = null;
        $pstmtselect = $this->con->prepare($this->selectticketdetails);
        $pstmtselect->bind_param('s', $referenceno);
        $pstmtselect->bind_result($this->departure, $this->fare, $this->status);
        $pstmtselect->execute();
        if ($pstmtselect->fetch()) {
            $data['departure'] = $this->departure;
            $data['fare'] = $this->fare;
            $data['status'] = $this->status;
        }
        return $data;
    }

    public function cancelTicket($referenceno) {
        $pstmtcancel = $this->con->prepare($this->cancelticket);
        $pstmtcancel->bind_param('s', $referenceno);
        $pstmtcancel->execute();
        if ($pstmtcancel->affected_rows > 0) {
            return true;
        }
        return false;
    }

    public function amendTicket($date, $departure, $arrival, $class, $shid, $referenceno) {
        $pstmtamend = $this->con->prepare($this->amendticket);
        $pstmtamend->bind_param('ssssii', $date, $departure, $arrival, $class, $shid, $referenceno);
        $pstmtamend->execute();
        if ($pstmtamend->affected_rows > 0) {
            return true;
        }
        return false;
    }

    public function updateQRCode($code, $referenceno) {
        $pstmtupdate = $this->con->prepare($this->updateqrcode);
        $pstmtupdate->bind_param('si', $code, $referenceno);
        $pstmtupdate->execute();
        if ($pstmtupdate->affected_rows > 0) {
            return true;
        }
        return false;
    }

}

?>
